USE CaseCentre;

CREATE SCHEMA [Admin]
    AUTHORIZATION [dbo];

CREATE TABLE [Admin].[DataSource](
	[ID] [int] primary key identity(1,1) not null,
	[Name] [varchar](50) NOT NULL);

CREATE TABLE [Admin].[Controls](
	[ID] [int] primary key identity(1,1) not null,
	[Name] [varchar](50) unique NOT NULL);

CREATE TABLE [Admin].[Form](
	[ID] [int] primary key identity(1,1) not null,
	[Name] [varchar](50) NOT NULL);

CREATE TABLE [Admin].[DataValueSource](
	[ID] [int] primary key  identity(1,1) not null,
	[DataSourceID] [int] REFERENCES [Admin].[DataSource] ([ID]),
	[Name] [varchar](50) NOT NULL,
	[Value] [varchar](50)
	);
	
CREATE TABLE [Admin].[FormDetail](
	[ID] [int] primary key identity(1,1) not null,
	[FormID] [int] REFERENCES [Admin].[Form] ([ID]),
	[ControlType]  [varchar](50) REFERENCES [Admin].[Controls] ([Name]),
	[ControlName]  [varchar](50),
	[LabelText] [varchar](50) NULL,
    [DisplayOrder] [int] NULL,
	[DataSourceID] [int] REFERENCES [Admin].[DataSource] ([ID])
	);

CREATE TABLE [Admin].[FormAnswer](
	[ID] [int] primary key identity(1,1) not null,
	[FormDetailID] [int]  REFERENCES [Admin].[FormDetail] ([ID]),
	[Answer] [varchar](50) NULL);

insert into [Admin].[Controls] values('checkbox'),('textbox'),('datetime'),('dropdown'),('radiobutton');


